package test;

import cn.edu.sdjzu.xg.bysj.domain.Department;

import java.sql.*;

public class DepartmentDao {
    public Department addWithStoredProcedure(Department department)
        throws SQLException,ClassNotFoundException{
        //加载驱动程序
        Class.forName("com.mysql.cj.jdbc.Driver");
        //url数据库连接字串，其中
        //jdbc为协议，mysql为子协议
        //localhost:3306为数据库服务器的地址和端口
        String url = "jdbc:mysql://localhost:3306/bysj" +
                "?useUnicode=true&characterEncoding=utf8" + //后面指定编码为utf8
                "&serverTimezone=Asia/Shanghai";//服务器时区为中国上海
        //用户名
        String username="root" ;
        //密码
        String password="240828chen";
        //获得连接对象
        Connection connection = DriverManager.getConnection(url,username,password);
        //根据连接对象准备可调用语句对象，sp_addDepartment为存储过程名称，后面为5个参数
        CallableStatement callableStatement =
                connection.prepareCall("CALL sp_addDepartment(?,?,?,?,?)");
        //将地5个参数设置为输出参数，类型为长整数（数据库的数据类型）
        callableStatement.registerOutParameter(5, Types.BIGINT);
        callableStatement.setString(1,department.getDescription());
        callableStatement.setString(2,department.getNo());
        callableStatement.setString(3,department.getRemarks());
        callableStatement.setInt(4,department.getSchool().getId());
        //执行可调用语句callableStatement
        callableStatement.execute();
        //获得第5个参数的值：数据库为该记录自动生成id
        int id = callableStatement.getInt(5);
        //为参数department的id字段赋值
        department.setId(id);
        callableStatement.close();
        connection.close();
        return department;
    }
}
